<?
	session_start();
	include('conn.php');
	//error_reporting(E_ALL);
	$q = $_REQUEST["q"];
	switch ($q)
	{
		case('1'):
		{
			$qry1 = "SELECT * from society";		
			$qry = mysql_query($qry1);
			$data = array();

			while($rows = mysql_fetch_array($qry))
			{
				$data[] = $rows['societyname'];
				/*$data[] = array
				(						
					"prefix" => $rows['societyprefix'],
					"name" => $rows['societyname']
				);*/
			}
			echo json_encode($data);
			break;
		}
		case('2'):
		{
				$soc = $_REQUEST["soc"];
				$qry1 = "select socid from society where societyname='$soc';";			
				$res1 = mysql_query($qry1);
				$row = mysql_fetch_array($res1);
				$sid = $row["socid"];
							
				//$soc = explode(" - ", $_REQUEST["soc"]);			
				$qrysel = "SELECT RoomNumber from Customer where socId ='$sid';"; 			
				$ressel = mysql_query($qrysel);				
				while($rows = mysql_fetch_array($ressel))
				{		
					$data[] = array
					(			
						"room" => $rows['RoomNumber']					
					);
				}
			echo json_encode($data);
			break;
		}
		case('3'):
		{
			$totalamt = 0;
			$bal = 0;
			$soc = $_REQUEST["soc"];
			$room = $_REQUEST["room"];
					
			$qry1 = "select socid from society where societyname='$soc';";			
			$res1 = mysql_query($qry1);
			$row1 = mysql_fetch_array($res1);
			$sid = $row1["socid"];
			
			$qry2 = "select CustId,CustomerName,TelNo from Customer where socId='$sid' and RoomNumber='$room';"; 			
			$res2 = mysql_query($qry2);
			$row2 = mysql_fetch_array($res2);
			$cid = $row2["CustId"];			
			
			$qry3= "SELECT stbtocustid,RateperMonth,ActivationDate,TerminationDate from STBtoCustomer where CustId='$cid'";
			$res3 = mysql_query($qry3);
			do{
				$row3 = mysql_fetch_array($res3);
				$scid = $row3['stbtocustid'];			
			}while(mysql_fetch_array($res3));			
			
			if (!is_null ($scid)){					
				$qrysel_soc = "select case when sum(payamount) is null then totalbillamount else  totalbillamount - sum(payamount) end as outstanding, totalbillamount from 
				(select tt.CustId, sum(cost) as totalbillamount from 
				(SELECT CustId, case when terminationdate is not null then (DATEDIFF(terminationdate,activationdate)/30)*ratepermonth 
				else (DATEDIFF(CURDATE(),activationdate)/30)*ratepermonth end as cost FROM 
				cableappdev.STBtoCustomer) tt  group by tt.CustId ) tt1 left join payments p on tt1.CustId = p.CustId 
				where tt1.CustId='$cid' group by p.CustId";
				$ressel_soc = mysql_fetch_array(mysql_query($qrysel_soc));
				$bal = $ressel_soc['outstanding'];				
			}											
						
			$notevar = '';						
			if(is_null($scid))
				$notevar = "Customer not mapped to any STB";
					
			$data = array
			(															
				"cname" => $row2['CustomerName'],											
				"phone" => $row2['TelNo'],
				"rpm" => $row3['RateperMonth'],
				"actdt" => $row3['ActivationDate'],
				"termdt" => $row3['TerminationDate'],
				"note" => $notevar,
				"balance" => $bal							
			);								
			echo json_encode($data);
			BREAK;
		}
		case('4'):
		{									
			$qrysel = "SELECT * from payments order by payid desc";		
			$qry = mysql_query($qrysel);
			$data = array();

			while($rows = mysql_fetch_array($qry))
			{
				$cid = $rows['CustId'];
				$qry1 = "select tt1.custId,CustomerName,case when sum(payamount) is null then totalbillamount else  totalbillamount - sum(payamount) end as outstanding, totalbillamount from 
				(select CustomerName,tt.CustId, sum(cost) as totalbillamount from 
				(SELECT CustomerName,sc.CustId, case when terminationdate is not null then (DATEDIFF(terminationdate,activationdate)/30)*sc.ratepermonth 
				else (DATEDIFF(CURDATE(),activationdate)/30)*sc.RateperMonth end as cost FROM 
				cableappdev.STBtoCustomer sc left join Customer c on sc.custId=c.custId) tt  group by tt.CustId ) tt1 left join payments p on tt1.CustId = p.CustId 
				where tt1.CustId='$cid' group by p.CustId;";
				$row1=mysql_fetch_array(mysql_query($qry1));
				$cname = $row1['CustomerName'];
				$rpm = $row1['RateperMonth'];
				$totaloutamt = $row1['outstanding'];
				//$totalpaidamt = $row1['tsum'];
			
				$sid = $rows['socId'];
				$qry2 = "select societyname from society where socid='$sid' ;";
				$row2=mysql_fetch_array(mysql_query($qry2));
				$sname = $row2['societyname'];
				
				$data[] = array
				(														
					"id" => $rows['payid'],
					"cname" => $cname,
					"sname" => $sname,
					"rpm" => $rpm,
					//"t_out" => $totaloutamt,
					//"t_paid" => $totalpaidamt,
					"balance" => $totaloutamt,
					"paydate" => $rows['paydate'],
					"payamount" => $rows['payamount'],
					"notes" => $rows['notes'],
					"RoomNumber" => $rows['RoomNumber'],
					"TelNo" => $rows['TelNo']
					//,"balance" => $rows['balance']
				);
			}
			echo json_encode($data);
			break;
		}
		default:
		{
			echo "No option selected";
			break;
		}
	}//end switch
	
	
?>